package db;
import entities.*;

import java.sql.*;
import java.util.*;

public class DBLoanManager {

	
static Connection con = null;
	
	static PreparedStatement getLoanedItems, addLoan, updateBookStatus;
	
	
	private static void getConnection() throws SQLException{
		if (!Connector.isValidConnection(con)) {
			con = Connector.getConnection();
			getLoanedItems = con.prepareStatement("select * from tblcopies left join tblloans on tblloans.ownerId=tblcopies.ownerId and tblloans.copyId=tblcopies.copyId left join tbluser" + 
					                        " on tblcopies.ownerId=tbluser.userID left join tblmasteritem on tblcopies.itemid=tblmasteritem.itemid " + 
					                        "where tblcopies.copyStatus='LOANED' or tblcopies.copyStatus='RECALLED' and tblloans.borrowerId=? order by lastchange desc");
    

		    addLoan = con.prepareStatement("insert into tblloans (ownerid, borrowerid,copyid) values (?,?,?)");
		    updateBookStatus=con.prepareStatement("update tblcopies set copyStatus = '" + Item.loaned + "' where  copyId=?");
		    
		}
	}
	
	public static boolean addLoan(int owner, int borrower, int copyId){
		try {
			//step 1: update the books table first
			getConnection();
			updateBookStatus.clearParameters();
			updateBookStatus.setInt(1, copyId); 
			int rs=updateBookStatus.executeUpdate();
			if(rs!=1)return false;
			//Step 2: add an entry in the loans table
			addLoan.clearParameters();
			addLoan.setInt(1, owner);
			addLoan.setInt(2,borrower);
			addLoan.setInt(3, copyId);
			rs=addLoan.executeUpdate();
			return (rs==1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
			
		}
	}
	public static ArrayList<Item> getLoanedItems(int borrowerId, int maxItems){
		try {
			getConnection();
			ArrayList<Item> items=new ArrayList<Item>();
			getLoanedItems.clearBatch();
			getLoanedItems.setInt(1, borrowerId);
			ResultSet rs=getLoanedItems.executeQuery();
			int cnt=0;
			while(rs.next()){
				if(maxItems>0 && cnt==maxItems)break;
				cnt++;
				Item i=new Item();
				i.setCopyId(rs.getInt("copyID"));
				i.setMasterId(rs.getInt("itemID"));
				i.setTitle(rs.getString("title"));
				i.setType(rs.getString("masterType"));				
				i.setOwner(rs.getString("firstName") + " " + rs.getString("lastName"));						
				i.setCopyStatus(rs.getString("copyStatus"));
				i.setLastChanged(rs.getDate("lastChange"));
				items.add(i);
			}
			return items;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		
	}
	

	
}
